IF SCHEMA_ID('tmp') IS NULL
	EXEC('CREATE SCHEMA [tmp]')
GO

IF OBJECT_ID('tmp.IMP_DE_Customer') IS NULL
BEGIN
	CREATE TABLE tmp.IMP_DE_Customer(
		LINK INT IDENTITY(1,1),
		IMP_Session_ID UNIQUEIDENTIFIER,
		IMP_Row_Number INT,
		PERSON_ID INT,
		CUSTOMER_ID INT,
		COUNTRY_ID INT,
		LOCATION_ID INT,
		ADDRESS_ID INT,
		SHIP_ADDRESS_ID INT,
		CONTACT_PERSON_ID INT,
		C_Error_Message NVARCHAR(1000),

		NAME NVARCHAR(255),
		FIRSTNAME NVARCHAR(128),
		LASTNAME NVARCHAR(128),
		[TYPE] INT,
		CATEGORY NVARCHAR(255),
		PHONE NVARCHAR(50),
		FAX NVARCHAR(50),
		EMAIL NVARCHAR(80),
		[ADDRESS] NVARCHAR(255),
		CITY NVARCHAR(255),
		[STATE] NVARCHAR(255),
		COUNTRY NVARCHAR(100),
		Finger_Size NVARCHAR(255),
		ZIPPOSTAL_CODE NVARCHAR(50),
		SHIP_IS_BILL INT,
		SHIP_ADDRESS NVARCHAR(255),
		SHIP_CITY NVARCHAR(255),
		SHIP_STATE NVARCHAR(255),
		SHIP_COUNTRY NVARCHAR(100),
		SHIP_ZIPPOSTAL_CODE NVARCHAR(50),
		ACCOUNT_ID INT,
		CURRENCY NVARCHAR(255),
		BALANCE NVARCHAR(255),
		BALANCE_DATE NVARCHAR(255),
		PAYMENT_TERMS_DAYS NVARCHAR(255),
		PAYMENT_ACCOUNT_ID INT,
		USERNAME NVARCHAR(255),
		[PASSWORD] NVARCHAR(255),
		PRICE_LEVEL INT,
		CREDIT_LIMIT MONEY,
		TAX_STATUS NVARCHAR(255),
		TAX_CODE NVARCHAR(255),
		Contact_First_Name NVARCHAR(128),
		Contact_Last_Name NVARCHAR(128),
		Contact_Phone NVARCHAR(50),
		Contact_Fax NVARCHAR(50),
		Contact_Email NVARCHAR(90),
		Contact_Address NVARCHAR(255),
		Contact_City NVARCHAR(255),
		Contact_State NVARCHAR(255),
		Contact_Country NVARCHAR(100),
		Contact_ZiP_PostalCode NVARCHAR(50),
		Contact_Position NVARCHAR(128),
		Contact_Department NVARCHAR(128),
		Contact_Note NVARCHAR(255),
		CONSTRAINT PK_tmp_IMP_DE_Customer PRIMARY KEY NONCLUSTERED (LINK)
	)
	EXEC sp_addextendedproperty 'MS_Description', 'Staging table for Customer import', 'schema', 'tmp', 'table', 'IMP_DE_Customer', DEFAULT, DEFAULT
END
GO

IF OBJECT_ID('tmp.SP_IMP_DE_Customer') IS NOT NULL
	DROP PROCEDURE tmp.SP_IMP_DE_Customer
GO

-- returns 1 in case of successfull import
-- (that means there were no exceptions during procedure,
-- but the data still may me invalid and for example no actual customers were created)
-- or 0 if any provisioned error stopped the import
CREATE PROCEDURE tmp.SP_IMP_DE_Customer
(
	@IMP_Session_ID UNIQUEIDENTIFIER,			-- import session identifier
	@Status NVARCHAR(1000) = NULL OUTPUT,		-- import status
	@Company_ID INT = 1,						-- Company.ID
	@Default_Country VARCHAR(128) = 'CANADA',	-- default COUNTRY (when null)
	@Default_City VARCHAR(255) = 'ONTARIO'		-- default CITY (when null)
)
AS
BEGIN
	SET NOCOUNT, XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL ON
	SET NUMERIC_ROUNDABORT, CURSOR_CLOSE_ON_COMMIT OFF

	IF NOT EXISTS (SELECT * FROM tmp.IMP_DE_Customer WHERE IMP_Session_ID = @IMP_Session_ID)
	BEGIN
		SET @Status = 'Staging table is empty. Import is stopped'
		RETURN 0
	END

	-- simple data checking
	UPDATE t
	SET t.C_Error_Message = 'No customer name. '
	FROM tmp.IMP_DE_Customer t
	WHERE t.IMP_Session_ID = @IMP_Session_ID
		AND t.NAME IS NULL
	-->
	UPDATE t
	SET t.C_Error_Message = ISNULL(t.C_Error_Message, '') + 'Customer already exists. '
	FROM tmp.IMP_DE_Customer t
	INNER JOIN dbo.DE_PERSON p
		ON p.NAME = t.NAME
		AND (p.EMAIL = t.EMAIL
			OR (p.EMAIL IS NULL AND t.EMAIL IS NULL)
			)
	INNER JOIN dbo.DE_CUSTOMER c
		ON c.PERSON_ID = p.ID
	WHERE t.IMP_Session_ID = @IMP_Session_ID
	-->
	UPDATE t
	SET t.C_Error_Message = ISNULL(t.C_Error_Message, '') + 'There''s another row (' + CONVERT(varchar(10), t2.IMP_Row_Number) + ') with same Name and Email. '
	FROM tmp.IMP_DE_Customer t
	INNER JOIN tmp.IMP_DE_Customer t2
		ON t2.NAME = t.NAME
		AND (t2.EMAIL = t.EMAIL OR (t2.EMAIL IS NULL AND t.EMAIL IS NULL))
		AND t2.IMP_Session_ID = t.IMP_Session_ID
		AND t2.LINK < t.LINK
	WHERE t.IMP_Session_ID = @IMP_Session_ID
	-- any other checks...

	UPDATE t
	SET t.COUNTRY = @Default_Country
	FROM tmp.IMP_DE_Customer t
	WHERE t.IMP_Session_ID = @IMP_Session_ID
		AND t.COUNTRY IS NULL
	UPDATE t
	SET t.[STATE] = @Default_City
	FROM tmp.IMP_DE_Customer t
	WHERE t.IMP_Session_ID = @IMP_Session_ID
		AND t.[STATE] IS NULL

	-- checking and updating FK values (or generating error message)
	UPDATE t
	SET t.COUNTRY_ID = c.COUNTRY_ID,
		t.C_Error_Message = ISNULL(t.C_Error_Message, '') +
			CASE WHEN c.COUNTRY_ID IS NULL THEN 'Country not found. ' ELSE '' END
	FROM tmp.IMP_DE_Customer t
	LEFT JOIN dbo.DE_COUNTRY c
		ON c.NAME = t.COUNTRY
	WHERE t.IMP_Session_ID = @IMP_Session_ID
	-->
	UPDATE t
	SET t.LOCATION_ID = c.LOCATION_ID,
		t.C_Error_Message = ISNULL(t.C_Error_Message, '') +
			CASE WHEN c.LOCATION_ID IS NULL THEN 'State not found. ' ELSE '' END
	FROM tmp.IMP_DE_Customer t
	LEFT JOIN dbo.DE_LOCATION c
		ON c.NAME = t.[STATE]
		AND c.COUNTRY_ID = t.COUNTRY_ID
	WHERE t.IMP_Session_ID = @IMP_Session_ID
	-->
	UPDATE t
	SET t.C_Error_Message = ISNULL(t.C_Error_Message, '') +
			CASE WHEN c.ACCOUNT_ID IS NULL THEN 'Account_ID not found. ' ELSE '' END
	FROM tmp.IMP_DE_Customer t
	LEFT JOIN dbo.AE_CHART_OF_ACCOUNTS c
		ON c.ACCOUNT_ID = t.ACCOUNT_ID
		AND c.[TYPE] = 'r'
	WHERE t.IMP_Session_ID = @IMP_Session_ID
	-->
	/*UPDATE t
	SET t.C_Error_Message = ISNULL(t.C_Error_Message, '') +
			CASE WHEN c.JURISDICTION_ID IS NULL THEN 'Tax_ID incorrect. ' ELSE '' END
	FROM tmp.IMP_DE_Customer t
	LEFT JOIN dbo.DE_TAX_JURISDICTION c
		ON c.JURISDICTION_ID = t.TAX_ID
	WHERE t.IMP_Session_ID = @IMP_Session_ID**/

	UPDATE t
	SET t.C_Error_Message = NULL
	FROM tmp.IMP_DE_Customer t
	WHERE t.IMP_Session_ID = @IMP_Session_ID
		AND LTRIM(RTRIM(t.C_Error_Message)) = ''

	IF NOT EXISTS (SELECT * FROM tmp.IMP_DE_Customer WHERE IMP_Session_ID = @IMP_Session_ID AND C_Error_Message IS NULL)
	BEGIN
		SET @Status = 'Staging table contains only invalid rows. Import is stopped'
		RETURN 0
	END

	DECLARE @InsertedCustomersCount INT, @InsertedPersonsCount INT, @InsertedAddressesCount INT

	BEGIN TRAN
		-- adding person address along with shipping address (if that's not empty)
		INSERT INTO dbo.DE_ADDRESS (NAME, POSTALCODE, ADDR_LINE, CITY, LOCATION_ID, COUNTY, COUNTRY_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE, FAX, ISSHIP)
		SELECT
			t2.NAME, t2.ZIPPOSTAL_CODE, t2.[ADDRESS], t2.CITY, t2.LOCATION_ID, t2.COUNTRY, t2.COUNTRY_ID, t2.FIRSTNAME, t2.LASTNAME, t2.EMAIL, t2.PHONE, t2.FAX, t2.ISSHIP
		FROM (SELECT
				t.NAME, t.ZIPPOSTAL_CODE, t.[ADDRESS], t.CITY, t.LOCATION_ID, t.COUNTRY, c.COUNTRY_ID, t.FIRSTNAME, t.LASTNAME, t.EMAIL, t.PHONE, t.FAX, 0 AS ISSHIP
			FROM tmp.IMP_DE_Customer t
			LEFT JOIN dbo.DE_COUNTRY c
				ON c.NAME = t.COUNTRY
			WHERE t.IMP_Session_ID = @IMP_Session_ID
				AND t.C_Error_Message IS NULL
			UNION ALL
			SELECT
				t.NAME, t.SHIP_ZIPPOSTAL_CODE, t.SHIP_ADDRESS, t.SHIP_CITY, sl.LOCATION_ID, t.SHIP_COUNTRY, sc.COUNTRY_ID, t.FIRSTNAME, t.LASTNAME, t.EMAIL, t.PHONE, t.FAX, 1 AS ISSHIP
			FROM tmp.IMP_DE_Customer t
			LEFT JOIN dbo.DE_COUNTRY sc
				ON sc.NAME = t.SHIP_COUNTRY
			LEFT JOIN dbo.DE_LOCATION sl
				ON sl.COUNTRY_ID = sc.COUNTRY_ID
				AND sl.NAME = t.SHIP_STATE
			WHERE t.IMP_Session_ID = @IMP_Session_ID
				AND t.C_Error_Message IS NULL
				AND COALESCE(t.SHIP_ADDRESS, t.SHIP_CITY, t.SHIP_STATE, t.SHIP_COUNTRY) IS NOT NULL
		) AS t2
		SET @InsertedAddressesCount = @@ROWCOUNT

		-- now we have address_id and (if any of ship address fields aren't empty) shipping_address_id
		UPDATE t
		SET ADDRESS_ID = ia.ID
		FROM tmp.IMP_DE_Customer t
		INNER JOIN dbo.DE_ADDRESS ia
			ON ia.NAME = t.NAME
			AND (ia.ADDR_LINE = t.[ADDRESS] OR (ia.ADDR_LINE IS NULL AND t.[ADDRESS] IS NULL))
			AND (ia.FIRST_NAME = t.FIRSTNAME OR (ia.FIRST_NAME IS NULL AND t.FIRSTNAME IS NULL)) 
			AND (ia.LAST_NAME = t.LASTNAME OR (ia.LAST_NAME IS NULL AND t.LASTNAME IS NULL)) 
			AND (ia.EMAIL = t.EMAIL OR (ia.EMAIL IS NULL AND t.EMAIL IS NULL)) 
			AND (ia.PHONE = t.PHONE OR (ia.PHONE IS NULL AND t.PHONE IS NULL))
			AND ia.ISSHIP = 0
		WHERE t.IMP_Session_ID = @IMP_Session_ID

		IF NOT EXISTS (SELECT * FROM tmp.IMP_DE_Customer WHERE IMP_Session_ID = @IMP_Session_ID AND ADDRESS_ID IS NOT NULL)
		BEGIN
			SET @Status = 'No ADDRESS_IDs found. Import is stopped'
			ROLLBACK
			RETURN 0
		END

		-- insert Persons
		INSERT INTO dbo.DE_PERSON (NAME, FIRST_NAME, LAST_NAME, EMAIL, PHONE, FAX, [TYPE_ID], COMPANY_ID, ADDRESS_ID, CLASS_NAME)
			SELECT
				t.NAME			AS NAME,
				t.FIRSTNAME		AS FIRST_NAME,
				t.LASTNAME		AS LAST_NAME,
				t.EMAIL			AS EMAIL,
				t.PHONE			AS PHONE,
				t.FAX			AS FAX,
				t.[TYPE]		AS [TYPE_ID],
				@Company_ID		AS COMPANY_ID,
				t.ADDRESS_ID	AS ADDRESS_ID,
				'CDECustomer'	AS CLASS_NAME
			FROM tmp.IMP_DE_Customer AS t
			WHERE t.IMP_Session_ID = @IMP_Session_ID
				AND t.C_Error_Message IS NULL
		SET @InsertedPersonsCount = @@ROWCOUNT

		-- now for every non-invalid source row we have newly inserted PERSON_ID
		UPDATE t
		SET PERSON_ID = ip.ID
		FROM tmp.IMP_DE_Customer t
		INNER JOIN dbo.DE_PERSON ip
			ON ip.NAME = t.NAME
			AND (ip.FIRST_NAME = t.FIRSTNAME OR (ip.FIRST_NAME IS NULL AND t.FIRSTNAME IS NULL)) 
			AND (ip.LAST_NAME = t.LASTNAME OR (ip.LAST_NAME IS NULL AND t.LASTNAME IS NULL)) 
			AND (ip.EMAIL = t.EMAIL OR (ip.EMAIL IS NULL AND t.EMAIL IS NULL)) 
			AND (ip.PHONE = t.PHONE OR (ip.PHONE IS NULL AND t.PHONE IS NULL))
			AND ip.[TYPE_ID] = t.[TYPE]
			AND ip.ADDRESS_ID = t.ADDRESS_ID
		WHERE t.IMP_Session_ID = @IMP_Session_ID

		IF NOT EXISTS (SELECT * FROM tmp.IMP_DE_Customer WHERE IMP_Session_ID = @IMP_Session_ID AND PERSON_ID IS NOT NULL)
		BEGIN
			SET @Status = 'No PERSON_IDs found. Import is stopped'
			ROLLBACK
			RETURN 0
		END

		-- adding customers at last
		INSERT INTO dbo.DE_CUSTOMER
		(
			PERSON_ID,
			CREDIT_LIMIT,
			SHIP_IS_BILL,
			USERNAME,
			[PASSWORD],
			AE_ACCOUNT_ID,
			TAX_CODE_ID,
			PRICE_LEVEL
		)
		SELECT
			t.PERSON_ID,
			ISNULL(t.CREDIT_LIMIT, 0)	AS CREDIT_LIMIT,
			t.SHIP_IS_BILL,
			t.USERNAME,
			t.[PASSWORD],
			t.ACCOUNT_ID,
			txc.TAX_CODE_ID,
			1							AS PRICE_LEVEL
		FROM tmp.IMP_DE_Customer t
		LEFT JOIN dbo.DE_TAX_CODE txc ON txc.CODE = t.TAX_CODE
		WHERE t.IMP_Session_ID = @IMP_Session_ID
			AND t.C_Error_Message IS NULL
		SET @InsertedCustomersCount = @@ROWCOUNT

		-- TODO: add contact person

		-- generate import status message
		SELECT @Status = 'Persons added: ' + CONVERT(VARCHAR(10), @InsertedPersonsCount)
		SELECT @Status = ISNULL(@Status + '. ', '') + 'Addresses added: ' + CONVERT(VARCHAR(10), @InsertedAddressesCount)
		SELECT @Status = ISNULL(@Status + '. ', '') + 'Customers added: ' + CONVERT(VARCHAR(10), @InsertedCustomersCount)
		SELECT @Status = ISNULL(@Status + '. ', '') + 'Rows are invalid: ' + CONVERT(VARCHAR(10), COUNT(*)) FROM tmp.IMP_DE_Customer WHERE IMP_Session_ID = @IMP_Session_ID AND C_Error_Message IS NOT NULL

	COMMIT

	RETURN 1
END
GO
